/*
 * Licensed to the 南京汇海交通科技有限公司
 *
 * Copyright (c) 2025.
 * All rights reserved.
 *
 * 本软件属南京汇海交通科技有限公司所有，在未获得南京汇海交通科技有限公司正式授权情况下，任何企业和个人，不能获取、阅读、安装、传播本软件涉及的任何受知识产权保护的内容。
 */
package com.chframework.poi;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.function.Consumer;
import jakarta.servlet.ServletResponse;
import jakarta.servlet.http.HttpServletResponse;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * 描述：使用原生POI封装Excel文档读写工具类
 * 日期：2022-02-28
 * 作者：周孝兵
 */
@Slf4j
@SuppressWarnings("unused")
public final class ExcelDocument {
// CHECKSTYLE:OFF

    private Workbook workbook;

    /**
     * 默认整体样式设置
     */
    private Consumer<Sheet> defaultSheetStyle;
    /**
     * 表头样式设置
     */
    private Consumer<Sheet> titleCellStyle;
    /**
     * 非表头样式设置
     */
    private Consumer<Sheet> contentCellStyle;

    public void setDefaultSheetStyle(Consumer<Sheet> defaultSheetStyle) {
        this.defaultSheetStyle = defaultSheetStyle;
    }

    public void setTitleCellStyle(Consumer<Sheet> titleCellStyle) {
        this.titleCellStyle = titleCellStyle;
    }

    public void setContentCellStyle(Consumer<Sheet> contentCellStyle) {
        this.contentCellStyle = contentCellStyle;
    }

    //region constructors

    /**
     * 创建空白工作薄，进行读写操作
     */
    public ExcelDocument(WorkbookType workbookType) {
        this.workbook = workbookType.create();
    }

    /**
     * 使用Excel文档创建工作薄，进行读写操作
     */
    public ExcelDocument(File file) {
        try {
            this.workbook = WorkbookFactory.create(new FileInputStream(file));
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }

    /**
     * 使用Excel文档创建工作薄，进行读写操作
     */
    public ExcelDocument(InputStream inStream) {
        try {
            this.workbook = WorkbookFactory.create(inStream);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }
    //endregion

    //region 将Java POJO列表写入Excel模板

    /**
     * 读取Excel模板，复制指定行单元格的样式到目标行，从指定的单元格开始写入数据
     *
     * @param dataGrid             要写入模板的数据集
     * @param startWriteCoordinate 从这个坐标开始写入数据集第一行第一列的值
     */
    public <T> void writeTemplateWithList(List<TreeMap<T, String>> dataGrid, String startWriteCoordinate) {
        writeTemplateWithList(dataGrid, null, startWriteCoordinate, startWriteCoordinate);
    }

    /**
     * 读取Excel模板，复制指定行单元格的样式到目标行，从指定的单元格开始写入数据
     *
     * @param dataGrid             要写入模板的数据集
     * @param sheetName            在哪个Sheet页操作
     * @param startWriteCoordinate 从这个坐标开始写入数据集第一行第一列的值
     */
    public <T> void writeTemplateWithList(List<TreeMap<T, String>> dataGrid, String sheetName, String startWriteCoordinate) {
        writeTemplateWithList(dataGrid, sheetName, startWriteCoordinate, startWriteCoordinate);
    }

    /**
     * 读取Excel模板，复制指定行单元格的样式到目标行，从指定的单元格开始写入数据
     *
     * @param dataGrid             要写入模板的数据集
     * @param sheetName            在哪个Sheet页操作
     * @param startWriteCoordinate 从这个坐标开始写入数据集第一行第一列的值
     * @param referStyleCoordinate 参考这个坐标所在单元格的样式复制行
     */
    private <T> void writeTemplateWithList(List<TreeMap<T, String>> dataGrid, String sheetName, String startWriteCoordinate, String referStyleCoordinate) {
        Sheet sheet = this.getSheet(sheetName);

        CellAddress startAddress = new CellAddress(startWriteCoordinate);
        int startRowNum = startAddress.getRow();
        int startColumnNum = startAddress.getColumn();

        CellAddress styleAddress = new CellAddress(referStyleCoordinate);
        int styleRowNum = styleAddress.getRow();
        Row styleRow1st = sheet.getRow(styleRowNum);
        Row styleRow2st = sheet.getRow(styleRowNum + 1);

        int j;
        int size = dataGrid.size();
        for (int i = 0; i < size; i++) {
            int currentRowNum = startRowNum + i;
            Row valueRow = sheet.getRow(currentRowNum);
            if (valueRow == null) {
                valueRow = sheet.createRow(currentRowNum);
            }
            if (i + 1 > 2) {
                if ((i + 1) % 2 == 0) {
                    copyRow(sheet, styleRow2st, valueRow);
                } else {
                    copyRow(sheet, styleRow1st, valueRow);
                }
            }
            j = 0;
            TreeMap<T, String> columns = dataGrid.get(i);
            for (Map.Entry<T, String> item : columns.entrySet()) {
                Cell cell;
                T key = item.getKey();
                String value = item.getValue();
                if (key instanceof String) {
                    CellAddress cellAddress = new CellAddress(String.valueOf(key));
                    cell = valueRow.getCell(cellAddress.getColumn());
                } else {
                    cell = valueRow.getCell(j++ + startColumnNum);
                }
                cell.setCellValue(value);
            }
        }
    }

    //endregion

    //region 将Java POJO写入Excel模板

    /**
     * 读取Excel模板文件，并根据坐标将结果集写入新创建的Excel文件, 如果已经读取Excel模板,可以使用这个方法反复对Excel模板进行
     * 写入，相同坐标的单元格再次写入时值会被覆盖
     *
     * @param dataGrid 具有唯一CellAddress(坐标)的单元格集合
     */
    public void writeTemplateWithObject(TreeMap<String, String> dataGrid) {
        writeTemplateWithObject(dataGrid, null);
    }

    /**
     * 读取Excel模板文件，并根据坐标将结果集写入新创建的Excel文件, 如果已经读取Excel模板,可以使用这个方法反复对Excel模板进行
     * 写入，相同坐标的单元格再次写入时值会被覆盖
     *
     * @param dataGrid  具有唯一CellAddress(坐标)的单元格集合
     * @param sheetName Sheet对象名称
     */
    public void writeTemplateWithObject(TreeMap<String, String> dataGrid, String sheetName) {
        this.writeWithObject(dataGrid, sheetName);
    }

    /**
     * 根据坐标将结果集写入新创建的Excel文件，相同坐标的单元格再次写入时值会被覆盖
     *
     * @param dataGrid 具有唯一CellAddress(坐标)的单元格集合
     */
    public void writeWithObject(TreeMap<String, String> dataGrid) {
        writeWithObject(dataGrid, null);
    }

    /**
     * 根据坐标将结果集写入新创建的Excel文件，相同坐标的单元格再次写入时值会被覆盖
     *
     * @param dataGrid  具有唯一CellAddress(坐标)的单元格集合
     * @param sheetName Sheet对象名称
     */
    public <T> void writeWithObject(TreeMap<String, String> dataGrid, String sheetName) {
        Sheet sheet = this.getSheet(sheetName);
        if (sheet == null) {
            sheet = this.createSheet(sheetName);
        }
        for (Map.Entry<String, String> element : dataGrid.entrySet()) {
            String cellCoordinate = element.getKey();
            String cellValue = element.getValue();
            CellAddress cellAddress = new CellAddress(cellCoordinate);
            int rowPosition = cellAddress.getRow();
            int columnPosition = cellAddress.getColumn();

            Row row = sheet.getRow(rowPosition);
            Cell cell = row.getCell(columnPosition);
            cell.setCellValue(cellValue);
        }
    }

    //endregion

    //region 将Java POJO列表写入新创建的Excel文件

    /**
     * 将结果集写入新创建的Excel文件并销毁Workbook对象
     *
     * @param dataGrid dataGrid 结果集
     */
    public <T> void writeWithList(List<TreeMap<T, String>> dataGrid) {
        writeWithList(dataGrid, null);
    }

    /**
     * 将结果集写入新创建的Excel文件并销毁Workbook对象
     *
     * @param dataGrid  结果集
     * @param sheetName 新建Sheet页名称
     */
    public <T> void writeWithList(List<TreeMap<T, String>> dataGrid, String sheetName) {
        writeWithList(dataGrid, sheetName, null);
    }

    /**
     * 将结果集写入新创建的Excel文件并销毁Workbook对象
     *
     * @param dataGrid       结果集
     * @param sheetName      新建Sheet页名称
     * @param cellCoordinate 从指定坐标开始写入
     */
    public <T> void writeWithList(List<TreeMap<T, String>> dataGrid, String sheetName, String cellCoordinate) {
        if (cellCoordinate == null || "".equals(cellCoordinate.trim())) {
            cellCoordinate = "A1";
        }
        CellAddress cellAddr = new CellAddress(cellCoordinate);
        int topOffset = cellAddr.getRow();
        int leftOffset = cellAddr.getColumn();
        Sheet sheet = this.createSheet(sheetName);
        int rowsCount = dataGrid.size();
        int j;
        for (int i = 0; i < rowsCount; i++) {
            Row row = sheet.getRow(i + topOffset);
            if (row == null) {
                row = sheet.createRow(i + topOffset);
            }
            TreeMap<T, String> columns = dataGrid.get(i);
            j = 0;
            for (Map.Entry<T, String> item : columns.entrySet()) {
                Cell cell;
                T key = item.getKey();
                String value = item.getValue();
                if (key instanceof String) {
                    CellAddress cellAddress = new CellAddress(String.valueOf(key));
                    cell = row.createCell(cellAddress.getColumn(), CellType.STRING);
                } else {
                    cell = row.createCell(j++ + leftOffset, CellType.STRING);
                }
                cell.setCellValue(value);
            }
        }
        // 设置标题栏样式
        if (titleCellStyle != null) {
            titleCellStyle.accept(sheet);
        }
        // 设置普通单元格样式
        if (contentCellStyle != null) {
            contentCellStyle.accept(sheet);
        }
    }

    //endregion

    //region 读取Excel文件返回Java POJO列表

    /**
     * 读取Excel（*.xlsx与*.xls均支持）返回指定Sheet页的数据集
     *
     * @return 返回读取Excel得到的结果集
     */
    public List<TreeMap<String, String>> readForList() {
        return readForList(null);
    }

    /**
     * 读取Excel（*.xlsx与*.xls均支持）返回指定Sheet页的数据集
     *
     * @param sheetName sheet页名称,默认值null或空字符串
     * @return 返回读取Excel得到的结果集
     */
    public List<TreeMap<String, String>> readForList(String sheetName) {
        return readForList(sheetName, null);
    }

    /**
     * 读取Excel（*.xlsx与*.xls均支持）返回指定Sheet页的数据集
     *
     * @param sheetName       sheet页名称,默认值null或空字符串
     * @param startCoordinate 从指定单元格开始读取
     * @return 返回读取Excel得到的结果集
     */
    public List<TreeMap<String, String>> readForList(String sheetName, String startCoordinate) {
        return readForList(sheetName, startCoordinate, null);
    }

    /**
     * 读取Excel（*.xlsx与*.xls均支持）返回指定Sheet页的数据集
     *
     * @param sheetName       sheet页名称,默认值null或空字符串
     * @param startCoordinate 从指定单元格开始读取
     * @param endCoordinate   到指定单元格结束读取
     * @return 返回读取Excel得到的结果集
     */
    public List<TreeMap<String, String>> readForList(String sheetName, String startCoordinate, String endCoordinate) {
        Sheet sheet = getSheet(sheetName);
        if (sheet == null) {
            return null;
        }
        return readSheet(sheet, startCoordinate, endCoordinate);
    }

    //endregion

    //region 读取Excel文件获取单元格的值

    /**
     * 读取Excel文件获取指定单元格的值
     *
     * @param cellCoordinateArray 坐标集合
     * @return 返回特定Sheet页指定单元格的数据集
     */
    public TreeMap<String, String> readForObject(String... cellCoordinateArray) {
        return this.readForObject(null, cellCoordinateArray);
    }

    /**
     * 读取Excel文件获取指定单元格的值
     *
     * @param sheetName           Sheet页名称
     * @param cellCoordinateArray 坐标集合
     * @return 返回特定Sheet页指定单元格的数据集
     */
    public TreeMap<String, String> readForObject(String sheetName, String... cellCoordinateArray) {
        TreeMap<String, String> dataGrids = new TreeMap<>(new ColumnComparator());

        if (cellCoordinateArray == null || cellCoordinateArray.length <= 0) {
            return dataGrids;
        }

        Sheet sheet = getSheet(sheetName);
        if (sheet == null) {
            return dataGrids;
        }

        List<TreeMap<String, String>> rowGrids = this.readSheet(sheet, null, null);
        HashMap<String, String> gridDataSet = new HashMap<>();
        for (TreeMap<String, String> rowGrid : rowGrids) {
            gridDataSet.putAll(rowGrid);
        }

        for (String cellCoordinate : cellCoordinateArray) {
            dataGrids.put(cellCoordinate, gridDataSet.getOrDefault(cellCoordinate, null));
        }
        return dataGrids;
    }

    //endregion

    //region support

    /**
     * 读取Excel文件指定的Sheet页
     *
     * @param sheet           Sheet页对象
     * @param startCoordinate 从指定单元格开始读取
     * @param endCoordinate   到指定单元格结束读取
     * @return 返回当前Sheet页由所有单元格组成的数据集合
     */
    private List<TreeMap<String, String>> readSheet(Sheet sheet, String startCoordinate, String endCoordinate) {
        List<TreeMap<String, String>> rowsGrid = new ArrayList<>();
        TreeMap<String, String> columnsGrid;

        CoordinateParser startCellAddr = new CoordinateParser(startCoordinate);
        int topOffSet = startCellAddr.getRow();
        int leftOffSet = startCellAddr.getColumn();

        CoordinateParser endCellAddr = new CoordinateParser(endCoordinate);
        int bottomOffSet = endCellAddr.getRow();
        int rightOffSet = endCellAddr.getColumn();

        int firstRowPosition = sheet.getFirstRowNum();
        int lastRowPosition = sheet.getLastRowNum();
        if (lastRowPosition == -1 || firstRowPosition == -1) {
            return rowsGrid;
        }
        if (topOffSet != 0) {
            firstRowPosition = topOffSet;
        }
        if (bottomOffSet != 0) {
            lastRowPosition = bottomOffSet;
        }
        for (int r = firstRowPosition; r <= lastRowPosition; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            columnsGrid = new TreeMap<>(new CoordinateComparator());
            int lastColumnPosition = row.getLastCellNum();
            int firstColumnPosition = row.getFirstCellNum();
            if (lastColumnPosition == -1 || firstColumnPosition == -1) {
                continue;
            }
            if (leftOffSet != 0) {
                firstColumnPosition = leftOffSet;
            }
            if (rightOffSet != 0) {
                lastColumnPosition = rightOffSet;
            }
            for (int c = firstColumnPosition; c <= lastColumnPosition; c++) {
                Cell cell = row.getCell(c);
                if (cell == null) {
                    continue;
                }
                String key = cell.getAddress().toString();
                String value = String.valueOf(cell);
                columnsGrid.put(key, value);
            }
            rowsGrid.add(columnsGrid);
        }
        return rowsGrid;
    }

    /**
     * 创建一个Sheet对象的副本
     *
     * @param sourceSheetName 要被克隆的Sheet对象名称
     * @param targetSheetName 新创建的Sheet对象的名称
     */
    public void copySheet(String sourceSheetName, String targetSheetName) {
        int sheetIndex = this.workbook.getSheetIndex(sourceSheetName);
        Sheet sheet = this.workbook.cloneSheet(sheetIndex);
        int targetSheetIndex = this.workbook.getSheetIndex(sheet);
        this.workbook.setSheetName(targetSheetIndex, targetSheetName);
    }

    /**
     * 行复制功能
     *
     * @param sheet     当前Sheet对象
     * @param sourceRow 从这个行对象复制样式
     * @param targetRow 将样式应用于这个行
     */
    private void copyRow(Sheet sheet, Row sourceRow, Row targetRow) {
        targetRow.setHeight(sourceRow.getHeight());
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress cellRangeAddress = sheet.getMergedRegion(i);
            if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
                CellRangeAddress newCellRangeAddress = new CellRangeAddress(targetRow.getRowNum(),
                        targetRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()),
                        cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
                sheet.addMergedRegion(newCellRangeAddress);
            }
        }
        Iterator<Cell> cellIt = sourceRow.cellIterator();
        while (cellIt.hasNext()) {
            Cell tmpCell = cellIt.next();
            Cell newCell = targetRow.createCell(tmpCell.getColumnIndex());
            copyCell(tmpCell, newCell);
        }
    }

    /**
     * 将源单元格复制到目标单元格
     *
     * @param sourceCell 源单元格
     * @param targetCell 目标单元格
     */
    private void copyCell(Cell sourceCell, Cell targetCell) {
        CellStyle targetStyle = this.workbook.createCellStyle();
        targetStyle.cloneStyleFrom(sourceCell.getCellStyle());
        targetCell.setCellStyle(targetStyle);

        if (sourceCell.getCellComment() != null) {
            targetCell.setCellComment(sourceCell.getCellComment());
        }

    }

    /**
     * 根据指定的Sheet名称从指定Excel工作簿对象Workbook查找Sheet对象，若未指定Sheet名称，默认获取第一个Sheet页对象
     *
     * @param sheetName Sheet名称
     * @return 返回指定的Sheet对象
     */
    private Sheet getSheet(String sheetName) {
        Sheet sheet = null;
        if (sheetName == null || "".equals(sheetName.trim())) {
            int sheetCount = this.workbook.getNumberOfSheets();
            if (sheetCount > 0) {
                sheet = this.workbook.getSheetAt(0);
            }
        } else {
            sheet = this.workbook.getSheet(sheetName);
        }
        return sheet;
    }

    /**
     * 删除指定名称的Sheet
     *
     * @param sheetName Sheet对象的名称
     */
    public void removeSheet(String sheetName) {
        int sheetIndex = this.workbook.getSheetIndex(sheetName);
        this.workbook.removeSheetAt(sheetIndex);
    }

    /**
     * 创建一个Sheet对象
     *
     * @param sheetName Sheet页名称
     * @return 返回新创建的Sheet对象，若具有相同名称的Sheet对象已经存在则直接返回
     */
    private Sheet createSheet(String sheetName) {
        Sheet sheet;
        if (sheetName == null || "".equals(sheetName.trim())) {
            int sheetCount = this.workbook.getNumberOfSheets();
            sheet = this.workbook.createSheet("Sheet" + (sheetCount + 1));
            if (this.defaultSheetStyle != null) {
                this.defaultSheetStyle.accept(sheet);
            }
        } else {
            sheet = this.workbook.getSheet(sheetName);
            if (sheet == null) {
                sheet = this.workbook.createSheet(sheetName);
                if (this.defaultSheetStyle != null) {
                    this.defaultSheetStyle.accept(sheet);
                }
            }
        }

        return sheet;
    }

    /**
     * 将当前工作薄写入Servlet响应对象
     *
     * @param response Servlet响应对象
     */
    private void exportWorkbookAs(ServletResponse response) {
        OutputStream outStream = null;
        try {
            outStream = new BufferedOutputStream(response.getOutputStream());
            this.workbook.write(outStream);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                if (outStream != null) {
                    outStream.flush();
                    outStream.close();
                }
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    //endregion

    //region others

    /**
     * 使用HttpServlet响应对象导出工作薄
     *
     * @param exportFileName 导出文件名称
     * @param response       HttpServlet响应对象
     */
    public void exportWorkbookAs(String exportFileName, HttpServletResponse response) {
        try {
            String fileName = java.net.URLEncoder.encode(exportFileName, "UTF-8");
            // fileName = new String(fileName.getBytes(), "ISO8859-1");
            response.reset();
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Accept-Ranges", "bytes"); // 如果存在Accept-Ranges标题，浏览器可能会尝试恢复中断的下载，而不是从头再次开始。
            response.setHeader("Access-Control-Allow-Origin", "*"); // 允许跨域获取请求资源
            response.setHeader("Content-Disposition", MessageFormat.format("attachment; filename=\"{0}\"", fileName));
            response.setContentType("application/octet-stream;charset=UTF-8");
            //response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            this.exportWorkbookAs(response);
        } catch (UnsupportedEncodingException e) {
            log.error(e.getMessage(), e);
        }
    }

    /**
     * 将Workbook对象写入到Excel文件并保存到指定位置
     *
     * @param saveFilePath Excel文件输出路径
     */
    public BufferedOutputStream saveWorkbookAs(String saveFilePath) {
        BufferedOutputStream outStream = null;
        try {
            if (this.workbook != null) {
                outStream = new BufferedOutputStream(new FileOutputStream(saveFilePath));
                this.workbook.write(outStream);
            }
        } catch (IOException e) {
            log.error("将Workbook对象写入到Excel文件并保存到指定位置的操作失败", e);
        } finally {
            try {
                if (outStream != null) {
                    outStream.flush();
                    outStream.close();
                }
            } catch (IOException e) {
                log.error("在finally代码块中清理Stream资源失败", e);
            }
        }

        // 之所以该方法会被如此改造，是因为参考了网友的建议，毕竟底层API不会无缘无故地向调用者抛出内存泄漏的风险：
        // https://stackoverflow.com/questions/23961553/method-may-fail-to-clean-up-stream-or-resource-on-checked-exception-findbugs?noredirect=1
        // 在finally代码块中，如果清理Stream资源的操作失败，那么程序员就需要在调用了该方法之后手动释放Stream资源，否则将会存在内存泄漏的风险。
        return outStream;
    }

    /**
     * 当不再对Workbook进行读写时应该调用这个方法来销毁该对象
     */
    public void closeWorkbook() {
        try {
            if (this.workbook != null) {
                this.workbook.close();
            }
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }

    //endregion
// CHECKSTYLE:ON
}
